package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialProductpurposeDAO;
import com.ph.irp.model.PBaseAssistantmaterialProductpurpose;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialProductpurposeDAOImpl implements
		PBaseAssistantmaterialProductpurposeDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7426234617869980619L;
	// 数据源
	private ComboPooledDataSource dataSource;

	// setter for dataSource
	public void setDataSource(ComboPooledDataSource dataSource) {

		this.dataSource = dataSource;

	}

	@Override
	public void addProductpurpose(
			PBaseAssistantmaterialProductpurpose productpurpose)
			throws Throwable {
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 新增sql语句
		StringBuilder sql = new StringBuilder();

		sql
				.append("insert into p_base_assistantmaterial_productpurpose(productpurposeCode,productpurposeName) ");
		sql.append("values('");
		sql.append(productpurpose.getProductPurposeCode() + "','");
		sql.append(productpurpose.getProductPurposeName() + "')");
		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql.toString());

			pStmt.executeUpdate();

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

	}

	@Override
	public void deleteProductpurpose(String productpurposeId) throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "delete from p_base_assistantmaterial_productpurpose where productpurposeId=?";

		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);

			pStmt.setString(1, productpurposeId);

			pStmt.execute();
		} catch (Exception e) {// 捕捉异常，并作相应的处理

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public List<PBaseAssistantmaterialProductpurpose> getAllproductpurposes()
			throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialProductpurpose> productpurposeList = new ArrayList<PBaseAssistantmaterialProductpurpose>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_productpurpose ";

		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);

			rs = pStmt.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (rs.next()) {
				PBaseAssistantmaterialProductpurpose productpurpose = new PBaseAssistantmaterialProductpurpose();

				productpurpose.setProductPurposeId(rs.getInt(1));

				productpurpose.setProductPurposeCode(rs.getString(2));

				productpurpose.setProductPurposeName(rs.getString(3));

				productpurposeList.add(productpurpose);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return productpurposeList;
	}

	@Override
	public List<PBaseAssistantmaterialProductpurpose> getProductpurposesByCode(
			String productpurposeCode) throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialProductpurpose> productpurposeList = new ArrayList<PBaseAssistantmaterialProductpurpose>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_productpurpose where productpurposecode like?";

		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);
			pStmt.setString(1, "%" + productpurposeCode + "%");
			rs = pStmt.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (rs.next()) {
				PBaseAssistantmaterialProductpurpose productpurpose = new PBaseAssistantmaterialProductpurpose();

				productpurpose.setProductPurposeId(rs.getInt(1));

				productpurpose.setProductPurposeCode(rs.getString(2));

				productpurpose.setProductPurposeName(rs.getString(3));

				productpurposeList.add(productpurpose);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return productpurposeList;
	}

	@Override
	public PBaseAssistantmaterialProductpurpose getProductpurposeById(
			Integer productpurposeId) throws Throwable {
		// TODO Auto-generated method stub
		// 辅助物料领料用途model
		PBaseAssistantmaterialProductpurpose productpurpose = new PBaseAssistantmaterialProductpurpose();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_productpurpose where productpurposeId=?";
		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, productpurposeId);
			rs = pStmt.executeQuery();
			// 处理结果集中的记录到采购价格model
			rs.next();
			productpurpose.setProductPurposeId(rs.getInt(1));

			productpurpose.setProductPurposeCode(rs.getString(2));

			productpurpose.setProductPurposeName(rs.getString(3));

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return productpurpose;
	}

	@Override
	public List<PBaseAssistantmaterialProductpurpose> getProductpurposesByName(
			String productpurposeName) throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialProductpurpose> productpurposeList = new ArrayList<PBaseAssistantmaterialProductpurpose>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_productpurpose where productpurposename like?";

		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);
			pStmt.setString(1, "%" + productpurposeName + "%");
			rs = pStmt.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (rs.next()) {
				PBaseAssistantmaterialProductpurpose productpurpose = new PBaseAssistantmaterialProductpurpose();

				productpurpose.setProductPurposeId(rs.getInt(1));

				productpurpose.setProductPurposeCode(rs.getString(2));

				productpurpose.setProductPurposeName(rs.getString(3));

				productpurposeList.add(productpurpose);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return productpurposeList;
	}

	@Override
	public List<PBaseAssistantmaterialProductpurpose> getProductpurposesByNameAndCode(
			String productpurposeName, String productpurposeCode)
			throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialProductpurpose> productpurposeList = new ArrayList<PBaseAssistantmaterialProductpurpose>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 结果集
		ResultSet rs = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_productpurpose where productpurposecode like? or productpurposename like?";

		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql);
			pStmt.setString(1, "%" + productpurposeCode + "%");
			pStmt.setString(2, "%" + productpurposeName + "%");
			rs = pStmt.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (rs.next()) {
				PBaseAssistantmaterialProductpurpose productpurpose = new PBaseAssistantmaterialProductpurpose();

				productpurpose.setProductPurposeId(rs.getInt(1));

				productpurpose.setProductPurposeCode(rs.getString(2));

				productpurpose.setProductPurposeName(rs.getString(3));

				productpurposeList.add(productpurpose);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return productpurposeList;
	}

	@Override
	public void updateProductpurpose(
			PBaseAssistantmaterialProductpurpose productpurpose)
			throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement pStmt = null;
		// 修改sql语句
		StringBuilder sql = new StringBuilder();

		sql
				.append("update p_base_assistantmaterial_productpurpose set productPurposeCode='");
		sql.append(productpurpose.getProductPurposeCode() + "',");
		sql.append("productPurposeName='"
				+ productpurpose.getProductPurposeName());
		sql.append("' where productPurposeId="
				+ productpurpose.getProductPurposeId());
		try {

			con = this.dataSource.getConnection();

			pStmt = con.prepareStatement(sql.toString());
			pStmt.executeUpdate();

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

	}

}
